﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace LogicUniversityBiz
{
    public class ApproveAdjustmentIssue
    {
        private int flag = 0;
        LogicUniversityEntities en = new LogicUniversityEntities();

        public IQueryable getNotApprovedAdjustment()
        {
            //List<ApproveAdjustmentView> list = new List<ApproveAdjustmentView>();
            //string sql = "select * from ApproveAdjustmentView where Status != 'Approved' and Total_Cost < 250 and Total_Cost > -250";
            //var issue = en.ExecuteStoreQuery<ApproveAdjustmentView>(sql);
           
            //foreach (ApproveAdjustmentView t in issue)
            //    list.Add(t);

            var y = from x in en.ApproveAdjustmentViews
                    where (x.Status != "Approved" && x.Total_Cost < 250 && x.Total_Cost > -250)
                    select new { x.Item_Code, x.Quantity_Adjusted, x.Reason, x.Total_Cost, x.Status };

            return y;

            
            
        }

        public void updateStatus()
        {
            using (LogicUniversityEntities en = new LogicUniversityEntities())
            {
                List<int> voucherID = new List<int>();
                string sql = "select Voucher_ID from ApproveAdjustmentView where Status != 'Approved' and Total_Cost < 250 and Total_Cost > -250";
                var notApprovedItem = en.ExecuteStoreQuery<int>(sql);
                foreach (int t in notApprovedItem)
                    voucherID.Add(t);
                

                for (int j = 0; j < voucherID.Count; j++)
                {
                    int vid = voucherID[j];
                    Voucher_Detail vDetail = en.Voucher_Detail.First(i => i.Voucher_ID == vid);
                    Adjustment_Voucher adVoucher = en.Adjustment_Voucher.First(i => i.Voucher_ID == vid);
                    vDetail.Status = "Approved";
                    adVoucher.Approved_By = "Supervisor"; 
                }

                en.SaveChanges();
            }
        }

        public void updateStockCard(String itemCode, String description, int quan)
        {
            using (LogicUniversityEntities en = new LogicUniversityEntities())
            {
                int balance;
                var itemCodes = from x in en.Stock_Card select x.Item_Code;
                foreach (var x in itemCodes.ToList())
                {
                    if (x == itemCode)
                    {
                        
                            int stockCardID = en.Stock_Card.First(b => b.Item_Code == x).StockCard_ID;
                            int bal = en.Catelogues.First(a => a.Item_Code == x).Balance;
                            balance = bal + quan;

                            Stock_Card_Detail newStock = new Stock_Card_Detail();
                            newStock.Date = Convert.ToDateTime(DateTime.Now.ToShortDateString());
                            newStock.Quantity = quan;
                            newStock.Description = description;
                            newStock.Stock_Card_ID = stockCardID;
                            newStock.Balance = balance;
                            en.AddToStock_Card_Detail(newStock);
                            en.SaveChanges();

                            Catelogue updateCat = en.Catelogues.First(c => c.Item_Code == x);
                            updateCat.Balance = balance;

                            en.SaveChanges();
                            flag = 1;
                            break;
                    }

                    //else
                    //{
                       
                    //        int bal = en.Catelogue.First(a => a.Item_Code == itemCode).Balance;
                    //        balance = bal + quan;

                    //        Stock_Card newStock = new Stock_Card();
                    //        newStock.Item_Code = itemCode;
                    //        en.AddToStock_Card(newStock);
                    //        en.SaveChanges();

                    //        int stockCardID = newStock.StockCard_ID;

                    //        updateStockCardDetail(quan, balance, description, stockCardID, itemCode);

                    //        Catelogue updateCat = en.Catelogue.First(c => c.Item_Code == itemCode);
                    //        updateCat.Balance = balance;

                    //        en.SaveChanges();
                    //   }
                         
                }

                if (flag == 0)
                {
                    int bal = en.Catelogues.First(a => a.Item_Code == itemCode).Balance;
                    balance = bal + quan;

                    Stock_Card newStock = new Stock_Card();
                    newStock.Item_Code = itemCode;
                    en.AddToStock_Card(newStock);
                    en.SaveChanges();

                    int stockCardID = newStock.StockCard_ID;

                    updateStockCardDetail(quan, balance, description, stockCardID, itemCode);

                    Catelogue updateCat = en.Catelogues.First(c => c.Item_Code == itemCode);
                    updateCat.Balance = balance;

                    en.SaveChanges();
                }

            }
        }

        public void updateStockCardDetail(int quan, int bal, String desp, int stockId, String itemCode)
        {
            using (LogicUniversityEntities en = new LogicUniversityEntities())
            {
                Stock_Card_Detail stockDetail = new Stock_Card_Detail();

                stockDetail.Date = Convert.ToDateTime(DateTime.Now.ToShortDateString());
                stockDetail.Quantity = quan;
                stockDetail.Description = desp;
                stockDetail.Stock_Card_ID = stockId;
                stockDetail.Balance = bal;

                en.AddToStock_Card_Detail(stockDetail);
                en.SaveChanges();
            }
        }
    }
}
